package cn.gbase.jiangsu.data.transfer.service;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

import org.nutz.dao.Cnd;
import org.nutz.dao.ConnCallback;
import org.nutz.dao.Dao;
import org.nutz.dao.QueryResult;
import org.nutz.dao.Sqls;
import org.nutz.dao.pager.Pager;
import org.nutz.ioc.aop.Aop;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.service.IdEntityService;

import cn.gbase.jiangsu.data.transfer.bean.ProductSortTreeNode;
import cn.gbase.jiangsu.data.transfer.common.Constants;
import cn.gbase.jiangsu.data.transfer.common.SystemException;

@IocBean(args = { "refer:dao" })
public class ProductSortTreeNodeService extends IdEntityService<ProductSortTreeNode> {

	public ProductSortTreeNodeService() {
		super();
	}

	public ProductSortTreeNodeService(Dao dao) {
		super(dao);
	}

	/**
	 * 取得所有标准框架列表.
	 * 
	 * @return
	 */
	public String getTreePulldownList(int treeId) {
		StringBuilder builder = new StringBuilder();

		List<ProductSortTreeNode> treeList = this.dao().query(ProductSortTreeNode.class, Cnd.wrap("treeId=" + treeId));
		for (ProductSortTreeNode treeNode : treeList) {
			builder.append("<option value='");
			builder.append(treeNode.getId());
			builder.append("'>");
			builder.append(treeNode.getLevelName());
			builder.append("</option>");
		}
		return builder.toString();
	}

	/**
	 * 取得标准框架列表（分页查询）.
	 * 
	 * @return
	 */
	public QueryResult getStandTreeNodeList(int treeId, int pageNumber, String code, String name) {
		if (pageNumber <= 0) {
			pageNumber = 1;
		}

		// 拼接条件
		String cond = "pid>0 "; // 虚拟根节点不显示
		if (treeId > 0) {
			cond += " and treeId=" + treeId + " ";
		}
		if (code != null && code.length() > 0) {
			code = code.replace("'", ""); // 替换单引号
			cond += " and code like '%" + code + "%' ";
		}
		if (name != null && name.length() > 0) {
			name = name.replace("'", "");
			cond += " and name like '%" + name + "%' ";
		}
		Pager pager = this.dao().createPager(pageNumber, Constants.LIST_ONE_PAGE_SIZE);

		// 查询记录总数
		int recordCount = this.dao().count(ProductSortTreeNode.class, Cnd.wrap(cond));
		pager.setRecordCount(recordCount);

		// 没数据，直接返回
		if (recordCount == 0) {
			return new QueryResult(new ArrayList<ProductSortTreeNode>(0), pager);
		}

		// 计算页数
		int maxPageNumber = 0;
		if (recordCount % Constants.LIST_ONE_PAGE_SIZE == 0) {
			maxPageNumber = recordCount / Constants.LIST_ONE_PAGE_SIZE;
		} else {
			maxPageNumber = recordCount / Constants.LIST_ONE_PAGE_SIZE + 1;
		}
		if (pageNumber > maxPageNumber) {
			pager.setPageNumber(maxPageNumber);
		}

		// 查询
		List<ProductSortTreeNode> resultList = this.dao().query(ProductSortTreeNode.class, Cnd.wrap(cond), pager); // 当前页面显示记录

		// 返回
		return new QueryResult(resultList, pager);
	}

	/**
	 * 取得标准框架列表（分页查询）.
	 * 
	 * @return
	 */
	public List<ProductSortTreeNode> getStandTreeNodeList(int treeId) {

		// 拼接条件
		String cond = "pid>0 "; // 虚拟根节点不显示
		if (treeId > 0) {
			cond += " and treeId=" + treeId + " ";
		}
		// 查询
		List<ProductSortTreeNode> resultList = this.dao().query(ProductSortTreeNode.class, Cnd.wrap(cond)); // 当前页面显示记录

		// 返回
		return resultList;
	}

	public void addStandTreeRootNode(final ProductSortTreeNode node) throws SystemException {

		this.dao().run(new ConnCallback() {
			@Override
			public void invoke(Connection conn) throws Exception {

				CallableStatement callStmt = null;
				try {
					callStmt = conn.prepareCall("{call addTreeRootNode(?,?,?,?,?)}");
					callStmt.setInt(1, node.getTreeId());
					callStmt.setString(2, node.getCode());
					callStmt.setString(3, node.getName());
					callStmt.registerOutParameter(4, java.sql.Types.INTEGER);
					callStmt.registerOutParameter(5, java.sql.Types.VARCHAR);
					callStmt.executeUpdate();

					// 返回值
					int code = callStmt.getInt(4);
					if (code != 1000) {
						String msg = callStmt.getString(5);
						throw new SystemException(msg);
					}
				} finally {
					if (null != callStmt) {
						callStmt.close();
					}
				}
			}
		});
	}

	@Aop("validationInterceptor")
	public void addOrUpdStandTreeNode(final ProductSortTreeNode node) throws SystemException {
		if (node.getId() > 0) {
			this.dao().run(new ConnCallback() {
				@Override
				public void invoke(Connection conn) throws Exception {

					CallableStatement callStmt = null;
					try {
						callStmt = conn.prepareCall("{call editTreeNode(?,?,?,?,?,?)}");
						callStmt.setInt(1, node.getId());
						callStmt.setInt(2, node.getPid());
						callStmt.setString(3, node.getCode());
						callStmt.setString(4, node.getName());
						callStmt.registerOutParameter(5, java.sql.Types.INTEGER);
						callStmt.registerOutParameter(6, java.sql.Types.VARCHAR);
						callStmt.executeUpdate();

						// 返回值
						int code = callStmt.getInt(5);
						if (code != 1000) {
							String msg = callStmt.getString(6);
							throw new SystemException(msg);
							// DaoRunner会给转成DaoException
						}
					} finally {
						if (null != callStmt) {
							callStmt.close();
						}
					}
				}
			});
		} else {
			this.dao().run(new ConnCallback() {
				@Override
				public void invoke(Connection conn) throws Exception {

					CallableStatement callStmt = null;
					try {
						callStmt = conn.prepareCall("{call addTreeNode(?,?,?,?,?)}");
						callStmt.setInt(1, node.getPid());
						callStmt.setString(2, node.getCode());
						callStmt.setString(3, node.getName());
						callStmt.registerOutParameter(4, java.sql.Types.INTEGER);
						callStmt.registerOutParameter(5, java.sql.Types.VARCHAR);
						callStmt.executeUpdate();

						// 返回值
						int code = callStmt.getInt(4);
						if (code != 1000) {
							String msg = callStmt.getString(5);
							throw new SystemException(msg);
							// DaoRunner会给转成DaoException
						}
					} finally {
						if (null != callStmt) {
							callStmt.close();
						}
					}
				}
			});
		}
	}

	public ProductSortTreeNode getStandTreeNode(int nodeId) {
		return this.dao().fetch(ProductSortTreeNode.class, nodeId);
	}

	public void deleteStandTreeNode(final int nodeId) throws SystemException {

		this.dao().run(new ConnCallback() {
			@Override
			public void invoke(Connection conn) throws Exception {

				CallableStatement callStmt = null;
				try {
					callStmt = conn.prepareCall("{call deleteTreeNode(?,?,?)}");
					callStmt.setInt(1, nodeId);
					callStmt.registerOutParameter(2, java.sql.Types.INTEGER);
					callStmt.registerOutParameter(3, java.sql.Types.VARCHAR);
					callStmt.executeUpdate();

					// 返回值
					int code = callStmt.getInt(2);
					if (code != 1000) {
						String msg = callStmt.getString(3);
						throw new SystemException(msg);
					}
				} finally {
					if (null != callStmt) {
						callStmt.close();
					}
				}
			}
		});
	}

	/**
	 * 取得节点的所有后代节点（包括自己）
	 * 
	 * @param nodeId
	 *            节点
	 * @return
	 */
	public List<ProductSortTreeNode> getDescendantNodeList(int nodeId) {
		ProductSortTreeNode node = getStandTreeNode(nodeId);
		if (node == null) {
			return new ArrayList<ProductSortTreeNode>(0);
		}

		// 取得小树杈的所有节点，包括自己
		List<ProductSortTreeNode> subList = this.dao().query(
				ProductSortTreeNode.class,
				Cnd.wrap(" treeId=" + node.getTreeId() + " and leftId>=" + node.getLeftId() + " and leftId<="
						+ node.getRightId()));
		return subList;
	}

	/**
	 * 取得节点的所有后代节点的ID，形式为“1,2,3,4”（包括自己）
	 * 
	 * @param nodeId
	 *            节点
	 * @return
	 */
	public String getDescendantNodeIdStr(int nodeId) {

		// 取得小树杈的所有节点，包括自己
		List<ProductSortTreeNode> subList = getDescendantNodeList(nodeId);
		if (subList.size() == 0) {
			return "";
		}
		StringBuilder sb = new StringBuilder();
		for (ProductSortTreeNode node : subList) {
			sb.append(node.getId());
			sb.append(",");
		}
		sb.deleteCharAt(sb.length() - 1);
		return sb.toString();
	}

	// ajax展开树
	public String getSubNodeData(ProductSortTreeNode node) {
		// 数据格式
		// [{ id:'0331', name:'n3.3.n1', isParent:true},{ id:'0332',
		// name:'n3.3.n2', isParent:false}]
		// 传过来的ID是父ID
		List<ProductSortTreeNode> subList = this.dao().query(ProductSortTreeNode.class,
				Cnd.wrap(" treeId=" + node.getTreeId() + " and pid=" + node.getId()));
		if (subList.size() > 0) {
			StringBuilder builder = new StringBuilder();
			builder.append("[");
			for (ProductSortTreeNode subNode : subList) {
				builder.append("{id:'").append(subNode.getId()).append("',");
				builder.append("name:'").append(subNode.getName()).append("',");

				// 判断是否有子节点
				if (subNode.getChildCount() > 0) {
					builder.append("isParent:true");
				} else {
					builder.append("isParent:false");
				}
				builder.append("},");
			}
			builder.deleteCharAt(builder.length() - 1);
			builder.append("]");
			return builder.toString();
		} else {
			return "[]";
		}
	}

	public List<ProductSortTreeNode> getChildNodeList(ProductSortTreeNode node) {

		return this.dao().query(ProductSortTreeNode.class,
				Cnd.wrap(" treeId=" + node.getTreeId() + " and pid=" + node.getId()));
	}

	public QueryResult getStandLinkList(int pageNumber, String findType, String standCode, String standName,
			int nodeId, String isLink) {

		if (pageNumber <= 0) {
			pageNumber = 1;
		}

		// 拼接条件
		String cond = "";
		if (standCode != null && standCode.length() > 0) {
			standCode = Sqls.escapteConditionValue(standCode).toString();
			standCode = standCode.replaceAll("[\\s　]+", ""); // 去掉空格
			// standCode = HalfFullCharConvert.full2Half(standCode); //全角转半角
			if ("full".equals(findType)) {
				cond += " and standCodeTrim like '%" + standCode + "%' ";
			} else if ("left".equals(findType)) {
				cond += " and standCodeTrim like '" + standCode + "%' ";
			} else if ("right".equals(findType)) {
				cond += " and standCodeTrim like '%" + standCode + "' ";
			} else {
				cond += " and standCodeTrim='" + standCode + "' ";
			}
		}
		if (standName != null && standName.length() > 0) {
			standName = standName.replace("'", "");
			cond += " and standName like '%" + standName + "%' ";
		}

		// 分页对象
		Pager pager = this.dao().createPager(pageNumber, Constants.LIST_ONE_PAGE_SIZE);

		// List<StandardLocal> resultList = null;
		//
		// // 关联状态
		// if (isLink != null && isLink.equals("Y")) {
		// // 已关联的，查找standard_local_link_view就可以。
		//
		// // 仅仅是节点自己关联的标准
		// if (nodeId > 0) {
		// cond += " and nodeId=" + nodeId;
		// }
		//
		// // 检索数据
		// Sql sql = Sqls
		// .create("select * from standard_local_link_view where 1=1 "
		// + cond);
		// sql.setCallback(Sqls.callback.entities());
		// sql.setPager(pager);
		// sql.setEntity(this.dao().getEntity(StandardLocal.class));
		// this.dao().execute(sql);
		// resultList = sql.getList(StandardLocal.class);
		//
		// // 记录数
		// sql = Sqls
		// .create("select count(*) from standard_local_link_view where 1=1 "
		// + cond);
		// sql.setCallback(Sqls.callback.longValue());
		// this.dao().execute(sql);
		// int recordCount = sql.getObject(Integer.class);
		// pager.setRecordCount(recordCount);
		//
		// } else {
		// // 未关联的，也就是可以进行关联的，则是所有标准，减去该树所有节点关联的标准。
		// // 因为一个标准在一个树里只能关联一次。
		//
		// // 取得树ID
		// int treeId = 0;
		// ProductSortTreeNode node = this.getStandTreeNode(nodeId);
		// if (node != null) {
		// treeId = node.getTreeId();
		// }
		//
		// // 检索数据
		// //String sqlStr =
		// "select * from standard_local where id not in (select id from standard_local_link_view where treeId = <treeId>) ";
		// String sqlStr =
		// "select * from standard_local AA left join (select id from standard_local_link_view where treeId = <treeId>) BB on AA.id = BB.id where BB.id is NULL ";
		// sqlStr = sqlStr.replace("<treeId>", "" + treeId);
		// sqlStr = sqlStr + cond;
		//
		// Sql sql = Sqls.create(sqlStr);
		// sql.setCallback(Sqls.callback.entities());
		// sql.setPager(pager);
		// sql.setEntity(this.dao().getEntity(StandardLocal.class));
		// this.dao().execute(sql);
		// resultList = sql.getList(StandardLocal.class);
		//
		// // 记录数
		// sqlStr = sqlStr.replace("select *", "select count(*)");
		// sql = Sqls.create(sqlStr);
		// sql.setCallback(Sqls.callback.longValue());
		// this.dao().execute(sql);
		// int recordCount = sql.getObject(Integer.class);
		// pager.setRecordCount(recordCount);
		//
		// }

		return new QueryResult(null, pager);
	}

	/**
	 * 取得下层子节点的列表.
	 * 
	 * @param nodeId
	 *            节点ID
	 * @return 子节点的列表
	 */
	public List<ProductSortTreeNode> getChildNodeList(int nodeId) {
		ProductSortTreeNode node = this.getStandTreeNode(nodeId);
		if (node == null) {
			return null;
		}
		return this.dao().query(ProductSortTreeNode.class,
				Cnd.wrap(" treeId=" + node.getTreeId() + " and pid=" + node.getId()));
	}

	/**
	 * 取得节点所在路径上的所有节点的列表(从根节点到该节点的所有节点).
	 * 
	 * @param nodeId
	 *            节点ID
	 * @return 路径上节点的列表
	 */
	public List<ProductSortTreeNode> getPathList(int nodeId) {
		ProductSortTreeNode node = this.getStandTreeNode(nodeId);
		if (node == null) {
			return null;
		}
		return this.dao().query(
				ProductSortTreeNode.class,
				Cnd.wrap(" treeId=" + node.getTreeId() + " and leftId<=" + node.getLeftId() + " and rightId>="
						+ node.getRightId() + " order by leftId"));
	}

	/**
	 * 取得树的根节点.
	 * 
	 * @param valueOf
	 */
	public ProductSortTreeNode getTreeRootNode(int treeId) {
		List<ProductSortTreeNode> list = this.dao().query(ProductSortTreeNode.class,
				Cnd.wrap(" treeId=" + treeId + " and pid=0"));
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}
}
